package com.ysd.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.ysd.entity.XiangXiXinWen;
import com.ysd.util.DBManager;

public class XiangXiXinWenDaoImpl {
	/**
	 * 通过xinWenId外键查询详细新闻表的数据
	 * @return
	 */
	public ArrayList<XiangXiXinWen> getXiangXiXinWen(int id) {
		Connection conn = DBManager.getConnection();
		ArrayList<XiangXiXinWen> list = new ArrayList<XiangXiXinWen>();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conn.createStatement();
			String sql = "SELECT *FROM ( SELECT *  FROM xiangxixinwen WHERE xinwenid="+id+"  ORDER BY RAND() LIMIT 5 ) AS x ORDER BY xiangXiXinWenId  ";
			rs = st.executeQuery(sql);
			while (rs.next()) {
				XiangXiXinWen xxxw = new XiangXiXinWen(rs.getInt("xiangXiXinWenId"), rs.getString("xinWenName"),
						rs.getString("xinWenNeiRong"), rs.getInt("xinWenId"));
				list.add(xxxw);
			}
			return list;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	
	
	/**
	 * 随机查询4条数据
	 * @return
	 */
	public ArrayList<XiangXiXinWen> getXinWenJingXuan() {
		Connection conn = DBManager.getConnection();
		ArrayList<XiangXiXinWen> list = new ArrayList<XiangXiXinWen>();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conn.createStatement();
			String sql = "SELECT * FROM xiangxixinwen ORDER BY RAND() limit 4";
			rs = st.executeQuery(sql);
			while (rs.next()) {
				String xinWenName= rs.getString("xinWenName");
				if(xinWenName.length()>20) {
					xinWenName=xinWenName.substring(0, 20);	
				}
				String xinWenNeiRong=	rs.getString("xinWenNeiRong");
				if(xinWenNeiRong.length()>100) {
					xinWenNeiRong=xinWenNeiRong.substring(0, 100);	
				}
				XiangXiXinWen xxxw = new XiangXiXinWen(rs.getInt("xiangXiXinWenId"),xinWenName,
						xinWenNeiRong, rs.getInt("xinWenId"));
				list.add(xxxw);
			}
			
			return list;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	
	
	/**
	 * 通过id显示1条大于这个id的数据
	 * @param id
	 * @return
	 */
	public ArrayList<XiangXiXinWen> getXiaYiPian(int id) {
		Connection conn = DBManager.getConnection();
		ArrayList<XiangXiXinWen> list = new ArrayList<XiangXiXinWen>();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conn.createStatement();
			String sql = "SELECT * FROM xiangxixinwen   WHERE xiangXiXinWenId  >(SELECT xiangXiXinWenId FROM xiangxixinwen where xiangXiXinWenId="+id+")  ORDER BY xiangXiXinWenId LIMIT 1";
			rs = st.executeQuery(sql);
			while (rs.next()) {
				String xinWenName=	rs.getString("xinWenName");
				if(xinWenName.length()>16) {
					xinWenName=xinWenName.substring(0, 16);	
				}
				XiangXiXinWen xxxw = new XiangXiXinWen(rs.getInt("xiangXiXinWenId"), xinWenName,
						rs.getString("xinWenNeiRong"), rs.getInt("xinWenId"));
				list.add(xxxw);
			}
			return list;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	/**
	 * 通过id显示1条小于这个id的数据
	 * @param id
	 * @return
	 */
	public ArrayList<XiangXiXinWen> getShangYiPian(int id) {
		Connection conn = DBManager.getConnection();
		ArrayList<XiangXiXinWen> list = new ArrayList<XiangXiXinWen>();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conn.createStatement();
			String sql = "SELECT * FROM xiangxixinwen   WHERE xiangXiXinWenId  <(SELECT xiangXiXinWenId FROM xiangxixinwen where xiangXiXinWenId="+id+")  ORDER BY xiangXiXinWenId desc LIMIT 1";
			rs = st.executeQuery(sql);
			while (rs.next()) {
				String xinWenName=	rs.getString("xinWenName");
				if(xinWenName.length()>16) {
					xinWenName=xinWenName.substring(0, 16);	
				}
				XiangXiXinWen xxxw = new XiangXiXinWen(rs.getInt("xiangXiXinWenId"), xinWenName,
						rs.getString("xinWenNeiRong"), rs.getInt("xinWenId"));
				list.add(xxxw);
			}
			return list;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	
	
	/**
	 * 查询新闻详情
	 * @return
	 */
	public ArrayList<XiangXiXinWen> selXinWenXiangQing() {
		Connection conn = DBManager.getConnection();
		ArrayList<XiangXiXinWen> list = new ArrayList<XiangXiXinWen>();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conn.createStatement();
			String sql = "SELECT * from xinwen,xiangxixinwen WHERE xinwen.xinWenId=xiangxixinwen.xinWenId";
			rs = st.executeQuery(sql);
			while (rs.next()) {
				String xinWenName= rs.getString("xinWenName");
				
				XiangXiXinWen xxxw = new XiangXiXinWen(rs.getInt("xiangXiXinWenId"),xinWenName,
						rs.getString("xinWenNeiRong"), rs.getInt("xinWenId"),rs.getString("xinWenNameFenLei"));
				list.add(xxxw);
			}
			return list;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	
	
	
	/**
	 * 删除新闻详情
	 * @param id
	 * @return
	 */
	public int delXinWenXiangQingId(int id) {
		return DBManager.executeUpdate("delete from xiangxixinwen where xiangxixinwenId="+id);
	}
	
	
	
	/**
	 * 多条件查询详细新闻
	 * @return
	 */
	public ArrayList<XiangXiXinWen> selXinWenXiangQingWhere(XiangXiXinWen x,int page,int size) {
		Connection conn = DBManager.getConnection();
		ArrayList<XiangXiXinWen> list = new ArrayList<XiangXiXinWen>();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conn.createStatement();
			String sql = "select xw.*,x.xinWenNameFenLei from xiangxixinwen xw,xinwen x where 1=1";
			if(x.getXinWenId()>0) {
				sql+=" and x.xinWenId="+x.getXinWenId();
			}
			if(x.getXinWenName()!=null && !x.getXinWenName().equals("")) {
				sql+=" and xinWenName LIKE '%"+x.getXinWenName()+"%'";
			}
			if(x.getXinWenNeiRong()!=null && !x.getXinWenNeiRong().equals("")) {
				sql+=" and xinWenNeiRong like '%"+x.getXinWenNeiRong()+"%'";
			}
			sql+=" and x.xinWenId=xw.xinWenId limit "+(page-1)*size+","+size;
			rs = st.executeQuery(sql);
			while (rs.next()) {
				String xinWenName= rs.getString("xinWenName");
				
				XiangXiXinWen xxxw = new XiangXiXinWen(rs.getInt("xiangXiXinWenId"),xinWenName,
						rs.getString("xinWenNeiRong"), rs.getInt("xinWenId"),rs.getString("xinWenNameFenLei"));
				list.add(xxxw);
			}
			return list;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	
	public ArrayList<XiangXiXinWen> selXinWenXiangQingWhereSize(XiangXiXinWen x) {
		Connection conn = DBManager.getConnection();
		ArrayList<XiangXiXinWen> list = new ArrayList<XiangXiXinWen>();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conn.createStatement();
			String sql = "select xw.*,x.xinWenNameFenLei from xiangxixinwen xw,xinwen x where 1=1";
			if(x.getXinWenId()>0) {
				sql+=" and x.xinWenId="+x.getXinWenId();
			}
			if(x.getXinWenName()!=null && !x.getXinWenName().equals("")) {
				sql+=" and xinWenName LIKE '%"+x.getXinWenName()+"%'";
			}
			if(x.getXinWenNeiRong()!=null && !x.getXinWenNeiRong().equals("")) {
				sql+=" and xinWenNeiRong like '%"+x.getXinWenNeiRong()+"%'";
			}
			sql+=" and x.xinWenId=xw.xinWenId ";
			rs = st.executeQuery(sql);
			while (rs.next()) {
				String xinWenName= rs.getString("xinWenName");
				
				XiangXiXinWen xxxw = new XiangXiXinWen(rs.getInt("xiangXiXinWenId"),xinWenName,
						rs.getString("xinWenNeiRong"), rs.getInt("xinWenId"),rs.getString("xinWenNameFenLei"));
				list.add(xxxw);
			}
			return list;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	
	
	
	
	public int addXinWenXiangQing(XiangXiXinWen x) {
		return DBManager.executeUpdate("INSERT into xiangxixinwen VALUE(null,'"+x.getXinWenName()+"','"+x.getXinWenNeiRong()+"',"+x.getXinWenId()+")");
	}
	
	
	public ArrayList<XiangXiXinWen> selXiangXiXinWenByName(String name){
		ArrayList<XiangXiXinWen> list=new ArrayList<XiangXiXinWen>();
		Connection conn=DBManager.getConnection();
		Statement st=null;
		ResultSet rs=null;
		try {
			st=conn.createStatement();
			rs=st.executeQuery("SELECT * from xiangxixinwen WHERE xinWenName='"+name+"'");
			while(rs.next()) {
				int xiangXiXinWenId=rs.getInt("xiangXiXinWenId");
				String xinWenName=rs.getString("xinWenName");
				String xinWenNeiRong=rs.getString("xinWenNeiRong");
				int xinWenId=rs.getInt("xinWenId");
				XiangXiXinWen xxxw=new XiangXiXinWen(xiangXiXinWenId, xinWenName, xinWenNeiRong, xinWenId);
				list.add(xxxw);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}	
	}
	
	
	public XiangXiXinWen selXiangXiXinWenById(int id) {
		Connection conn = DBManager.getConnection();
		XiangXiXinWen xxxw=null;
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conn.createStatement();
			String sql = "SELECT * FROM xiangxixinwen WHERE xiangXiXinWenId="+id;
			rs = st.executeQuery(sql);
			while (rs.next()) {
				 xxxw = new XiangXiXinWen(rs.getInt("xiangXiXinWenId"), rs.getString("xinWenName"),
						rs.getString("xinWenNeiRong"), rs.getInt("xinWenId"));
				
			}
			return xxxw;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	
	
	public int editXiangXiXinWenById(XiangXiXinWen x) {
		return DBManager.executeUpdate("UPDATE xiangxixinwen set xinWenName='"+x.getXinWenName()+"',xinWenNeiRong='"+x.getXinWenNeiRong()+"',xinWenId='"+x.getXinWenId()+"' WHERE xiangXiXinWenId="+x.getXiangXiXinWenId());
	}
	
}
